﻿using System;
using System.Collections.Generic;
using System.Text;
using Model = SQLServerHealthCheck.Model;
using SQLServerHealthCheck.Model;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
using SMO = Microsoft.SqlServer.Management.Smo;
using WMI = Microsoft.SqlServer.Management.Smo.Wmi;
using System.Windows.Forms;
using System.IO;
using System.Collections;


namespace SQLServerHealthCheck.SQLServer
{
    public static class InstallationCheck
    {
        public static List<Exception> errors;
        public static ArrayList arrInstallationCheck = new ArrayList();
     
        public static List<Exception> GenerateServicesOverview(List<String> SQLServerInstances)
        {


            try
            {
                errors = new List<Exception>();
                     
               
                HealthCheckDataContext dc = new HealthCheckDataContext(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringTemplate"].ToString());

                dc.ClearAll();

                List<Server> servers = new List<Server>();

                //delte the Installation Check Log file if it exists
                //string InstallationCheckLogfile = Application.StartupPath + "\\SQLServerHealthCheck_InstallationCheckLog.txt";
               
                //if (File.Exists(InstallationCheckLogfile))
                //{
                //    File.Delete(InstallationCheckLogfile);
                //}
                arrInstallationCheck.Clear();

                FillServersAndSQLServerServices(SQLServerInstances, dc, servers);
                FillServerDrives(dc, servers);//Capture the details about the drive and free space
                FillServices(dc, servers);//Capture the details about different SQL sevices running in the servers
            }

            catch(System.Security.SecurityException ex1)
            {
                MessageBox.Show("You don't have permission to delete file in path " + Application.StartupPath, "HealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Error);
            } 


                       
                   
            return errors;
        }



        public static void FillServerDrives(HealthCheckDataContext dc, List<Server> servers)
        {

            foreach (Server server in servers)
            { 
                    FillServerDrives(dc, server);
             
            }

           
        }

        private static void FillServerDrives(HealthCheckDataContext dc, Server server)
        {
            try
            {
                //WriteLoginFile("SQLServerHealthCheck_InstallationCheckLog.txt", "FillServerDrives: ", " FillServerDrives method in Installation Check Started....");

                if (server.Services.Count(s => s.ServiceTypeID == (int)WMI.ManagedServiceType.SqlServer) > 0)
                {

                    Service sqlService = server.Services.First(s => s.ServiceTypeID == (int)WMI.ManagedServiceType.SqlServer);

                    if (IsSQLServerAccessible(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringTemplate"].Replace("{SQLServerInstance}", sqlService.InstanceName).Replace("{Database}", "Master")))
                    {



                        using (SqlConnection cn = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringTemplate"].Replace("{SQLServerInstance}", sqlService.InstanceName).Replace("{Database}", "Master")))
                        {
                            cn.Open();
                            String sql = @"

	        use master
	
	        DECLARE @OleAutomationSetting int
	
	        SET @OleAutomationSetting =(SELECT CAST(value_in_use as int) FROM SYS.configurations WHERE name='Ole Automation Procedures')
	
	        IF @OleAutomationSetting=0
	        BEGIN
		        exec sp_configure 'Ole Automation Procedures', 1;
		        reconfigure with override;
		
	        END

	        SET NOCOUNT ON  
	        DECLARE @hr int  
	        DECLARE @fso int  
	        DECLARE @drive char(1)  
	        DECLARE @odrive int  
	        DECLARE @TotalSize varchar(20)  
	        DECLARE @MB bigint ; 
	        SET @MB = 1048576  

	        CREATE TABLE #drives (  drive char(1) PRIMARY KEY,  FreeSpace int NULL,  TotalSize int NULL)  

	        INSERT #drives(drive,FreeSpace) 
	        EXEC master.dbo.xp_fixeddrives  

	        EXEC @hr=sp_OACreate 'Scripting.FileSystemObject', @fso OUT  
	        IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso  

	        DECLARE dcur 
	        CURSOR LOCAL FAST_FORWARD  
	        FOR 
		        SELECT drive 
		        from #drives  ORDER by drive  
		        OPEN dcur  
	        FETCH NEXT FROM dcur INTO @drive  

	        WHILE @@FETCH_STATUS=0  
	        BEGIN  
		        EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive  
		        IF @hr <> 0 
			        EXEC sp_OAGetErrorInfo @fso  
		        EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT  
		
		        IF @hr <> 0 
			        EXEC sp_OAGetErrorInfo @odrive  
		        UPDATE #drives  
		        SET TotalSize=@TotalSize/@MB  WHERE drive=@drive  
		
		        FETCH NEXT FROM dcur INTO @drive  
	        END  

	        CLOSE dcur  
	        DEALLOCATE dcur  
	        EXEC @hr=sp_OADestroy @fso  

	        IF @hr <> 0 
		        EXEC sp_OAGetErrorInfo @fso  
	        SELECT drive,  FreeSpace/1024.0 as 'Free',  TotalSize/1024.0 as 'Total',  CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)'  
	        FROM #drives  ORDER BY drive  
	        DROP TABLE #drives 

	        exec sp_configure 'Ole Automation Procedures', @OleAutomationSetting;
	        reconfigure with override;

        ";

                            SqlCommand cmd = new SqlCommand(sql, cn);
                            SqlDataReader rd = cmd.ExecuteReader();

                            while (rd.Read())
                            {
                                ServerDrive drive = new ServerDrive();

                                drive.Drive = rd.GetString(0).ToCharArray()[0];
                                drive.FreeSpaceInGB = rd.GetDecimal(1);
                                drive.TotalSpaceInGB = rd.GetDecimal(2);

                                server.ServerDrives.Add(drive);
                            }

                            rd.Close();
                        }

                        dc.SubmitChanges();

                        //WriteLoginFile("SQLServerHealthCheck_InstallationCheckLog.txt", "FillServerDrives: ", " FillServerDrives method in Installation Check Completed....");
                    }

                    else
                    {
                        //Add server name that has problem in connectivity
                    }
                }
            }
            catch (Exception ex)
            {                             
           
                errors.Add(ex);
                //WriteLoginFile("SQLServerHealthCheck_InstallationCheckLog.txt", "FillServerDrives: ", " Some error occured in Installation Check.Please check exception file for details");
            }

        }
                    


        public static void FillServices(HealthCheckDataContext dc, List<Server> servers)
        {

            foreach (Server server in servers)
            {
                FillServices(dc, server);

            }

            dc.SubmitChanges();
        }


        private static void FillServices(HealthCheckDataContext dc, Server server)
        {
            try
            {

                //WriteLoginFile("SQLServerHealthCheck_InstallationCheckLog.txt", "FillServices: ", " FillServices method in Installation Check Started....");

                WMI.ManagedComputer comp = new WMI.ManagedComputer(server.Name);



                foreach (WMI.Service service in comp.Services)
                {

                    Service s = null;
                    if (service.Type == WMI.ManagedServiceType.SqlServer)
                    {
                        String[] splitArray = service.Name.ToLower().Split('$');

                        String instanceName = splitArray.Length == 2 ? splitArray[1] : "";

                        if (server.Services.Count(s2 => s2.Name == instanceName) > 0)
                            s = server.Services.First(s2 => s2.Name == instanceName);
                        else
                        {
                            s = new Service();
                            s.Name = instanceName;
                            s.Server = server;
                        }

                    }

                    else
                    {
                        s = new Service();
                        s.Name = service.Name;
                        s.Server = server;
                    }

                    s.LogOnAs = service.ServiceAccount;
                    s.StartMode = service.StartMode.ToString();
                    s.ServiceTypeID = (int)service.Type;


                }

                dc.SubmitChanges();

                //WriteLoginFile("SQLServerHealthCheck_InstallationCheckLog.txt", "FillServices: ", " FillServices method in Installation Check Completed....");
            }
            catch (Exception ex)
            {
                              

                errors.Add(ex);
                //WriteLoginFile("SQLServerHealthCheck_InstallationCheckLog.txt", "FillServices: ", " Some error occured in Installation Check.Please check exception file for details");
            }

        }




        public static void FillServersAndSQLServerServices(List<String> SQLServerInstances, HealthCheckDataContext dc, List<Server> servers)
        {
            try
            {
                //WriteLoginFile("SQLServerHealthCheck_InstallationCheckLog.txt", "FillServersAndSQLServerServices: ", " FillServersAndSQLServerServices method in Installation Check Started..  ");
                foreach (string instance in SQLServerInstances)
                {
                    //Check the connection to SQL Server Instance and gets the details.

                    if (IsSQLServerAccessible(@"Server=" + instance.ToString() + ";database=master;Integrated Security=True"))
                    {



                        String[] parts = instance.ToLower().Split('\\');
                        String serverName = parts[0];

                        Server server;
                        Service service = new Service();
                        if (servers.Count(s => s.Name == serverName) == 0)
                        {
                            server = new Server();
                            server.Name = serverName;
                            dc.Servers.InsertOnSubmit(server);

                            servers.Add(server);
                        }
                        else
                        {
                            server = servers.First(s => s.Name == serverName);
                        }


                        service.Name = parts.Length == 2 ? parts[1] : "";
                        service.ServiceTypeID = (int)WMI.ManagedServiceType.SqlServer;
                        service.Server = server;

                        FillSQLServerServiceInfo(dc, service, instance);

                        dc.SubmitChanges();

                        //WriteLoginFile("SQLServerHealthCheck_InstallationCheckLog.txt", "FillServersAndSQLServerServices: ", " FillServersAndSQLServerServices method in Installation Check completed....");

                        FillServerInfo(dc, server);

                    }

                    else
                    {
                        //Add the name of SQL Intances for which connections were not sucessfull and details were not imported

                        arrInstallationCheck.Add(instance.ToString());
                        //WriteLoginFile("SQLServerHealthCheck_InstallationCheckLog.txt", "FillServersAndSQLServerServices: ", " There were problems in importig InstallationCheck details for " + instance.ToString() + ".Please check the connectivity to this instance");  
                    }

                }
            }
            catch (Exception ex)
            {
                
                errors.Add(ex);

                //WriteLoginFile("SQLServerHealthCheck_InstallationCheckLog.txt", "FillServersAndSQLServerServices: ", " Some error occured in Installation Check.Please check exception file for details");
            }
            
        }

        private static void FillServerInfo(HealthCheckDataContext dc, Server server)
        {
            try
            {
                //WriteLoginFile("SQLServerHealthCheck_InstallationCheckLog.txt", "FillServerInfo: ", " FillServerInfo method in Installation Check Started....");

                if (server.Services.Count(s => s.ServiceTypeID == (int)WMI.ManagedServiceType.SqlServer) > 0)
                {

                    Service sqlService = server.Services.First(s => s.ServiceTypeID == (int)WMI.ManagedServiceType.SqlServer);

                    using (SqlConnection cn = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringTemplate"].Replace("{SQLServerInstance}", sqlService.InstanceName).Replace("{Database}", "Master")))
                    {
                        cn.Open();

                        //enable xp_cmdshell using  sp_configure
                        string sqlOLE = "sp_configure 'show advanced options',1 ;reconfigure with override;";
                        SqlCommand OLEcmd = new SqlCommand(sqlOLE, cn);
                        OLEcmd.ExecuteNonQuery();

                        string sqlOLE1 = "sp_configure 'xp_cmdshell',1 ;reconfigure with override;";
                        SqlCommand OLEcmd1 = new SqlCommand(sqlOLE1, cn);
                        OLEcmd1.ExecuteNonQuery();

                        //enabling  Ole Automation Procedures using sp_configure ends



                        String sql = @"

/* well, this will work, though it is a little elaborate. it does however provide some other cool info about the processer(s)*/
DECLARE
@nProcessorCount INT
,@vcPhysicalMemory INT
,@vcProcessorNameString VARCHAR(60),
@OSversion varchar(50)

CREATE TABLE #tblStats 
(
[Index] INT,
[Name] VARCHAR(200),
Internal_Value VARCHAR(50),
Character_Value VARCHAR(200),
)
create table #tmp
(Details varchar(8000))
insert into #tmp(Details)
exec master..xp_cmdshell 'systeminfo'
select @OSversion=rtrim(ltrim(SUBSTRING(Details,9,LEN(Details)))) from #tmp where Details like 'OS Name%'



INSERT INTO #tblStats
EXEC master.dbo.xp_msver 'ProcessorCount'
INSERT INTO #tblStats
EXEC master.dbo.xp_msver 'PhysicalMemory'


SELECT @nProcessorCount = Internal_Value FROM #tblStats WHERE [Index] = 16
SELECT @vcPhysicalMemory = Internal_Value FROM #tblStats WHERE [Index] = 19

EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'HARDWARE\DESCRIPTION\System\CentralProcessor\0',
N'ProcessorNameString',
@vcProcessorNameString OUTPUT,
N'no_output' 

SELECT 
@nProcessorCount AS ProcessorCount
,@vcProcessorNameString AS ProcessorDescription
, @vcPhysicalMemory,@OSversion

DROP TABLE #tblStats
drop table #tmp


";

                        SqlCommand cmd = new SqlCommand(sql, cn);
                        SqlDataReader rd = cmd.ExecuteReader();

                        if (rd.Read())
                        {
                            server.ProcessorCount = rd.GetInt32(0);
                            server.ProcessorType = rd.GetString(1);
                            server.MemoryInGB = rd.GetInt32(2);
                            server.OSVersion = rd.GetString(3);
                        }

                        rd.Close();
                    }

                    dc.SubmitChanges();

                    //WriteLoginFile("SQLServerHealthCheck_InstallationCheckLog.txt", "FillServerInfo: ", " FillServerInfo method in Installation Check Completed....");

                }
            }
            catch (Exception ex)
            {

                               

                errors.Add(ex);
                //WriteLoginFile("SQLServerHealthCheck_InstallationCheckLog.txt", "FillServerInfo: ", " Some error occured in Installation Check.Please check exception file for details");
            }


        }

        private static void FillSQLServerServiceInfo(HealthCheckDataContext dc, Service service, string instance)
        {

            try
            {
                //WriteLoginFile("SQLServerHealthCheck_InstallationCheckLog.txt", "FillSQLServerServiceInfo: ", " FillSQLServerServiceInfo method in Installation Check Started....");

               using (SqlConnection cn = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringTemplate"].Replace("{SQLServerInstance}", service.InstanceName).Replace("{Database}", "master")))
               using (SqlConnection cn1 = new SqlConnection(@"Server=" + instance.ToString() +";Database=master;Integrated Security=True"))

                {
                 

                 
                    cn.Open();
                    cn1.Open();
                    //enable Ole Automation Procedures using sp_configure
                    string sqlOLE = "sp_configure 'show advanced options',1 ;reconfigure with override;";
                    SqlCommand OLEcmd = new SqlCommand(sqlOLE, cn);
                    OLEcmd.ExecuteNonQuery();

                    string sqlOLE1 = "sp_configure 'Ole Automation Procedures',1 ;reconfigure with override;";
                    SqlCommand OLEcmd1 = new SqlCommand(sqlOLE1, cn);
                    OLEcmd1.ExecuteNonQuery();

                    //enabling  Ole Automation Procedures using sp_configure ends




                   //Get product Version,Edition etc
                    String sql = @"SELECT 
	                        cast(SERVERPROPERTY('Edition' ) as varchar(100)) As Edition,
                            REPLACE( cast(SERVERPROPERTY('ProductVersion' )as varchar(13)), '.0.','.00.') AS ProductVersion,
                         SERVERPROPERTY ('productlevel') as Productlevel"
                        ;
                    SqlCommand cmd = new SqlCommand(sql, cn1);
                    SqlDataReader rd = cmd.ExecuteReader();

                    if (rd.Read())
                    {

                        service.ProductVersion = rd["ProductVersion"].ToString();
                        service.Edition = rd["Edition"].ToString();
                        //service.Description = dc.GetDescriptionFromProductVersion(service.ProductVersion);
                        service.Description = rd["Productlevel"].ToString();
                    }

                    rd.Close();
                }

                dc.SubmitChanges();

                //WriteLoginFile("SQLServerHealthCheck_InstallationCheckLog.txt", "FillSQLServerServiceInfo: ", " FillSQLServerServiceInfo method in Installation Check Completed....");

            }
             catch (Exception ex)
            {

                errors.Add(ex);

                //WriteLoginFile("SQLServerHealthCheck_InstallationCheckLog.txt", "FillSQLServerServiceInfo: ", " Some error occured in Installation Check.Please check exception file for details");
               
            }


        }



        public static bool IsSQLServerAccessible(string strconn)  
        {
           // WriteLoginFile("SQLServerHealthCheck_InstallationCheckLog.txt", "IsSQLServerAccessible: ", " IsSQLServerAccessible method in Installation Check Started for : " + strconn);

            SqlConnection conn = new SqlConnection(strconn);
            try
            {
                conn.Open();
            }
            catch (SqlException sqle1)
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                    return true;
                }
                else
                {
                    //WriteLoginFile("SQLServerHealthCheck_InstallationCheckLog.txt", "IsSQLServerAccessible: ", "Connection failed for  : " + strconn);
                    return false;
                }
            }
            catch (Exception e1)
            {
                errors.Add(e1);
                //WriteLoginFile("SQLServerHealthCheck_InstallationCheckLog.txt", "IsSQLServerAccessible: ", " Some error occured for : " + strconn);
            }

            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
              
            }

           // WriteLoginFile("SQLServerHealthCheck_InstallationCheckLog.txt", "IsSQLServerAccessible: ", " IsSQLServerAccessible method in Installation Check Completed for : " + strconn);
            return true;
        }

        public static void WriteExceptioninFile( string message)
        {
            try
            {
                if (!string.IsNullOrEmpty(message))
                {


                    File.AppendAllText(Application.StartupPath + "\\SQLServerHealthCheck_Exception.txt", (((System.DateTime.Now + " - ") + " - ")  + " - ") + message + Environment.NewLine);

                }


            }
            catch (System.Security.SecurityException ex1)
            {
                MessageBox.Show("You don't have permission to create file in path " + Application.StartupPath, "HealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            catch (Exception ex)
            {

                errors.Add(ex);

                //WriteLoginFile("SQLServerHealthCheck_InstallationCheckLog.txt", "WriteLoginFile: ", " Some error occured in Installation Check.Please check exception file for details");

            }
        }



        public static void WriteLoginFile(string filename,string methodName, string message)
        {
            try
            {
                if (!string.IsNullOrEmpty(message))
                {
                   

                    File.AppendAllText(Application.StartupPath + "\\" + filename, (((System.DateTime.Now + " - ") + " - ") + methodName + " - ") + message + Environment.NewLine);
                    
                }


            }
            catch (System.Security.SecurityException ex1)
            {
                MessageBox.Show("You don't have permission to create file in path " + Application.StartupPath, "HealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            catch (Exception ex)
            {

                errors.Add(ex);

                //WriteLoginFile("SQLServerHealthCheck_InstallationCheckLog.txt","WriteLoginFile: ", " Some error occured in Installation Check.Please check exception file for details");

            }
        } 


    }


}
